package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;

import ca.ualberta.cs.BibTex.Author;
import ca.ualberta.cs.CLRDB.interfaces.EntityRelManagement;

public class AuthoringManagement extends EntityRelManagement {
		
	
	public AuthoringManagement(){
		mainTableName = "Authoring";
	}
	
	public void initialize(Statement statement) throws SQLException{
		
		try{
			statement.executeUpdate("DROP TABLE Authoring");
		}catch(Exception e){}
		
		statement.executeUpdate("CREATE TABLE Authoring ( " +					
				"personId INTEGER NOT NULL, " +
				"resourceId INTEGER NOT NULL, " +					
				"CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
                	"REFERENCES Resource (ID) ON DELETE CASCADE, " +
                "CONSTRAINT FK_personid FOREIGN KEY (personId) " +
                	"REFERENCES Person (ID) ON DELETE CASCADE, " +                    
                "CONSTRAINT PK_authoring PRIMARY KEY (resourceId, personId) " +     
		") IN CLRSPACE");
		numOfCols = 2;
	}
	
	/**
	 * 
	 * @param resourceId
	 * @param authors
	 * @param statement
	 * @return the id of the first author
	 * @throws SQLException
	 */
	public int addAuthoringAndAuthors(Integer resourceId, ArrayList<Author> authors, Statement statement) throws SQLException{
		
		//in this step, we doesn't know whether an author named A. Test who authors paper1 is 
		//the same person as A. Test who authors paper2. We could check emails, affiliations, but,
		//an author may use more than one email and may have been in different institutions. 
		//So, we ignore it for now and will use post processing with aid of the authors themselves to distinguish them
				
		//FIXME email to the users who match this authors name, ask to fill a form and say if these 
		//newly annotated papers are theirs. When form is filled update database.
		
		int id = -1;
		
		try{
			for(Author author: authors){
				//insert author as a NEW person (considering the above explanation)
				String name = author.toString();
				
				User user = new User();
				user.setName(name);
				user.setEmail(author.getEmail());
				user.setAffiliation(author.getAffiliation());
				
				Integer authorId = DatabaseController.personsMgmt.addAuthor(user);
				if(id == -1)
					id = authorId;
				/*
				String keys = "(name" + author.getAffiliation()!=null? ", affiliation":"" + author.getEmail()!=null?", email":"" +")";
				String vals = "("+name+ author.getAffiliation()!=null? ", "+author.getAffiliation():"" + author.getEmail()!=null?", "+author.getEmail():"" +")";
				statement.executeUpdate("INSERT INTO Person "+ keys+ " VALUES "+ vals+"", Statement.RETURN_GENERATED_KEYS);
				ResultSet res = statement.getGeneratedKeys();
				res.next();
				Integer authorId = res.getInt(1);
				*/
				if(authorId != -1)
					statement.executeUpdate("INSERT INTO Authoring (personId, resourceId) VALUES (" + authorId + ", " + resourceId +")");
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		
		return id;
	}
	
	public int addAuthoring(Integer resourceId, Integer bibtexId, ArrayList<Author> authors, Statement statement) throws SQLException{
		
		int id = -1;
		
		try{
			for(Author author: authors){			
				
				Integer authorId = findThisAuthorsIdByBib(bibtexId, author, statement);
				if(id == -1)
					id = authorId;
				
				if( authorId != -1)
					statement.executeUpdate("INSERT INTO Authoring (personId, resourceId) VALUES (" + authorId + ", " + resourceId +")");
				else
					//throw exception with full info?!
					//statement.executeUpdate("INSERT INTO Authoring (personId, resourceId) VALUES (" + authorId + ", " + resourceId +")");
					addAuthoringAndAuthors(resourceId, authors, statement);
			}
		}
		catch(Exception e){
			System.out.println("***** caught:");
			e.printStackTrace();
			System.out.println("******");
		}
		
		return id;
	}
	

	public int findThisAuthorsIdByBib(Integer bibtexId, Author author, Statement statement) throws SQLException{
		int personId = -1;
		
		ResultSet result = statement.executeQuery("SELECT p.ID FROM Person p, Authoring a, Resource r WHERE " +
				"p.ID = a.personId AND p.name = '"+author.toString()+"' AND a.resourceId = r.ID AND " +
						"r.bibtexId = "+bibtexId);
		
		if(result.next())
			personId = result.getInt("ID");
		/*if(result.next())
			System.out.println("---------->>>> WHY two author records for one bibtex and author name?");*/
		
		if(personId == -1){
			System.out.println("Inside 'findThisAuthorsIdByBib', author not found:");
			System.out.println("bibtexId:"+bibtexId+",author:"+author.toString());
		}
		
		return personId;
	}
	
	public int findThisAuthorsIdByResource(Integer resourceId, Author author, Statement statement) throws SQLException{
		int personId = -1;
		
		ResultSet result = statement.executeQuery("SELECT p.ID FROM Person p, Authoring a WHERE " +
				"p.name = '"+author.toString()+"' AND a.resourceId = "+resourceId +" AND " +
						"a.personId = p.ID");
		
		if(result.next())
			personId = result.getInt("ID");
		
		if(personId == -1){
			System.out.println("Inside 'findThisAuthorsIdByResource', author not found:");
			System.out.println("resourceId:"+resourceId+",author:"+author.toString());
		}
		
		return personId;
	}

	public void setRole(String role) {
		// TODO Auto-generated method stub
		
	}

	public void resetRole() {
		// TODO Auto-generated method stub
		
	}
	
	
}
